﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


/// <summary>
/// Summary description for AlbumDAO
/// </summary>
public class AlbumDAO
{
    public DataTable GetListAlbum()
    {
        using (SqlConnection conn = UtilityHelper.GetConnection())
        {
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                string strSQL = "SELECT TOP 9 a.* ," + Album.Price + "," + Artist.ArtistName +
                                " FROM " + Table.Album + " a, " + Table.Artist + " ar" + 
                                " WHERE a." + Artist.ArtistID + " = ar." + Artist.ArtistID;
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                reader = cmd.ExecuteReader();
                DataTable table = new DataTable();
                table.Load(reader);
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    reader.Close();
                    conn.Close();
                }
            }
        }
    }
    public AlbumDTO GetAlbumItemFromDataReader(SqlDataReader reader)
    {
        AlbumDTO albumDTO = new AlbumDTO();
        albumDTO.AlbumID = int.Parse(reader[Album.AlbumID].ToString());
        albumDTO.ArtistID = int.Parse(reader[Album.ArtistID].ToString());
        albumDTO.GenreID = int.Parse(reader[Album.GenreID].ToString());
        albumDTO.AlbumArtUrl = reader[Album.AlbumArtUrl].ToString();
        albumDTO.Price = decimal.Parse(reader[Album.Price].ToString());
        albumDTO.Title = reader[Album.Title].ToString();

        return albumDTO;
    }
    public AlbumDTO GetAlbumItemByAlbumID(int AlbumID)
    {
        using (SqlConnection conn = UtilityHelper.GetConnection()) 
        {
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                string strSQL = "SELECT * FROM "  + Table.Album + 
                                " Where " + Album.AlbumID + " = " + AlbumID;
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                reader = cmd.ExecuteReader();
                AlbumDTO albumDTO = new AlbumDTO();
                while( reader.Read() )
                {
                    albumDTO = GetAlbumItemFromDataReader(reader);
                }
                
                return albumDTO;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ( conn != null) 
                {
                    reader.Close();
                    conn.Close();
                }
            }
        }
    }
}